package com.yangjun.dao.impl.realtimeLiveAnalysis.realtimeSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.alibaba.druid.sql.ast.statement.SQLIfStatement.Else;
import com.fasterxml.jackson.annotation.JsonTypeInfo.As;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yangjun.common.realtimesource.ChartDefine;
import com.yangjun.entity.realtimeliveAnalysis.KeywordInforEntity;
import com.yangjun.entity.realtimeliveAnalysis.RegionInforEntity;

@Repository
public class WebImpl {
	
	 @Autowired
	 @Resource(name = "bikadJdbcTemplate")
	 private JdbcTemplate mJdbcTemplate;
	 
	 /***
	  * 平台流量总览数据
	  * @param today
	  * @param contrastday
	  * @param ordersources 平台ID链表
	  * @return
	  */
	 public List<Map<String, Object>> getPlatformOverview(String today,String yestoday, String contrastday, List<String> ordersources) {
		 String sql = "select to_char(t.DATE_DAY, 'YYYY-MM-DD') as data_date,sum(t.CLIENT_NUMBER) as CLIENT_NUMBER, "
			   		+ "sum(t.PV) as PV, "
			   		+ "sum(t.NEW_CLIENT) as NEW_CLIENT, "
			   		+ "nvl(round(sum(t.JUMP_NUMBER) / sum(t.SOURCE_NUMBER), 4),0) as JUMP_RATE, "
			   		+ "nvl(round(sum(t.VISITDEPTH) / sum(t.CLIENT_NUMBER), 3),0) as AVG_VISITDEPTH, "
			   		+ "nvl(round((sum(ABS(t.SUM_VISITLENGTH)) / sum(t.MULTI_PV_CLIENT)) / 1000, 0),0) as AVG_VISITLENGTH "
			   		+ "from (select CLIENT_NUMBER, "
			   			+ "PV, "
			   			+ "NEW_CLIENT, "
			   			+ "SUM_VISITLENGTH, "
			   			+ "MULTI_PV_CLIENT, "
			   			+ "JUMP_NUMBER, "
			   			+ "SOURCE_NUMBER, "
			   			//+ "CLIENT_NUMBER * (JUMP_RATE / 100.0) as JUMP_CLIENT, "
			   			+ "CLIENT_NUMBER * AVG_VISITDEPTH as VISITDEPTH, "
			   			// + "CLIENT_NUMBER * AVG_VISITLENGTH as VISITLENGTH, "
			   			+ "DATE_DAY, "
			   			+ "SITE_ID "
			   			+ "from DV_LIVE_PLATFORM_SUMMARIZE_NL "
			   			+ "where DATE_DAY in(date'" + contrastday + "', date'" + yestoday + "', date'" + today + "') "
				   		+ "and SITE_ID in(:ordersources)"
				   		+ ") t "
			   		+ "group by t.DATE_DAY order by t.DATE_DAY";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 parameters.addValue("ordersources", ordersources);
			    
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 
//		 System.err.println("sql ===== " + sql);
		        
		 return rows;
	 }
	 
	 /**
	  * 渠道流量总览数据
	  * @param today
	  * @param contrastday
	  * @param ordersources	平台ID链表
	  * @param channel		一级渠道
	  * @return
	  */
	 public List<Map<String, Object>> getChannelOverview(String today,String yestoday, String contrastday, List<String> ordersources, String channel) {
		 
		 String sql = "select to_char(t.DATE_DAY, 'YYYY-MM-DD') as data_date,sum(t.CLIENT_NUMBER) as CLIENT_NUMBER, "
			   		+ "sum(t.PV) as PV, "
			   		+ "sum(t.NEW_CLIENT) as NEW_CLIENT, "
			   		+ "nvl(round(sum(t.JUMP_NUMBER) / sum(t.SOURCE_NUMBER), 4),0) as JUMP_RATE, "
			   		+ "nvl(round(sum(t.VISITDEPTH) / sum(t.CLIENT_NUMBER), 3),0) as AVG_VISITDEPTH, "
			   		+ "nvl(round((sum(ABS(t.SUM_VISITLENGTH)) / sum(t.MULTI_PV_CLIENT)) / 1000, 0),0) as AVG_VISITLENGTH "
			   		+ "from (select CLIENT_NUMBER, "
			   			+ "PV, "
			   			+ "NEW_CLIENT, "
			   			+ "SUM_VISITLENGTH, "
			   			+ "MULTI_PV_CLIENT, "
			   			+ "JUMP_NUMBER, "
			   			+ "SOURCE_NUMBER, "
			   			//+ "CLIENT_NUMBER * (JUMP_RATE / 100.0) as JUMP_CLIENT, "
			   			+ "CLIENT_NUMBER * AVG_VISITDEPTH as VISITDEPTH, "
			   			// + "CLIENT_NUMBER * AVG_VISITLENGTH as VISITLENGTH, "
			   			+ "DATE_DAY, "
			   			+ "SITE_ID,"
			   			+ "UTM_MEDIUM "
			   			+ "from DV_LIVE_PLATFORM_SUMMARIZE_NL "
			   			+ "where DATE_DAY in(date'" + contrastday + "', date'" + yestoday + "', date'" + today + "') "
				   		+ "and SITE_ID in(:ordersources) "
				   		+ "and department = '" + channel + "' "
			   			+ ") t "
			   		+ "group by t.DATE_DAY order by t.DATE_DAY";
		 
//		 System.err.println("渠道流量总览数据： " + "(platform = " + ordersources + ") " + sql);
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 parameters.addValue("ordersources", ordersources);
		 
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		        
		 return rows;
	 }
	 
	 /**
	  * 折线图数据
	  * @param name
	  * @param today
	  * @param contrastday
	  * @param ordersources	平台ID链表
	  * @param firstChannel	一级渠道
	  * @param secondChannel	二级渠道
	  * @param landpage		登录页
	  * @return
	  */
	 public List<Map<String,Object>> getLineChartData(String name, String today, String contrastday, List<String> ordersources, String firstChannel, String secondChannel, String landpage) {
		 List<Map<String,Object>> rows = null;
		 String sql = "";
		 String firstChannelSQL = "";
		 String secondChannelSQL = "";
		 String landpageSQL = "";
		 
		 // 拼接筛选条件
		 if (!StringUtils.isBlank(firstChannel))
			 firstChannelSQL = " and department = '" + firstChannel + "' ";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 if (!StringUtils.isBlank(landpage))
			 landpageSQL = " and LANDPAGE = '" + landpage + "' ";
			
		 if (name == ChartDefine.CLIENT_NUMBER) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
					+ "d.T_HOUR as dataHour, "
					+ "round(sum(CLIENT_NUMBER),0) as CLIENT_NUMBER "
					+ "from DV_LIVE_PLATFORM_SUMMARIZE d "
					+ "where d.DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
					+ "and d.SITE_ID in (:ordersources) "
					+ firstChannelSQL + secondChannelSQL + landpageSQL
					+ " group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
			 
//			 System.out.println("访客数--"+sql);
		 } else if (name == ChartDefine.NEW_CLIENT) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
						+ "d.T_HOUR as dataHour, "
						+ "round(sum(NEW_CLIENT),0) as NEW_CLIENT "
						+ "from DV_LIVE_PLATFORM_SUMMARIZE d "
						+ "where d.DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
						+ "and d.SITE_ID in (:ordersources) "
						+ firstChannelSQL + secondChannelSQL
						+ " group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
		 } else if (name == ChartDefine.OLD_CLIENT) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
						+ "d.T_HOUR as dataHour, "
						+ "round(sum(CLIENT_NUMBER) - sum(NEW_CLIENT),0) as OLD_CLIENT "
						+ "from DV_LIVE_PLATFORM_SUMMARIZE d "
						+ "where d.DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
						+ "and d.SITE_ID in (:ordersources) "
						+ firstChannelSQL + secondChannelSQL
						+ " group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
		 } else if (name == ChartDefine.PV) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
						+ "d.T_HOUR as dataHour, "
						+ "round(sum(PV),0) as PV "
						+ "from DV_LIVE_PLATFORM_SUMMARIZE d "
						+ "where d.DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
						+ "and d.SITE_ID in (:ordersources) "
						+ "group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
		 } else if (name == ChartDefine.JUMP_RATE) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
						+ "d.T_HOUR as dataHour, "
						+ "round(sum(d.JUMP_NUMBER) / sum(d.SOURCE_NUMBER), 4) as JUMP_RATE "
						+ "from ("
						+ "		select CLIENT_NUMBER,"
						//+ "		CLIENT_NUMBER * JUMP_RATE / 100 as JUMP_CLIENT,"
						+ "     JUMP_NUMBER, "
						+ "     SOURCE_NUMBER, "
						+ "		T_HOUR,"
						+ "		SITE_ID, "
						+ "		DATE_DAY, "
						+ "		UTM_MEDIUM,"
						+ "		UTM_SOURCE "
						+ "		from DV_LIVE_PLATFORM_SUMMARIZE "
						+ "		where DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
						+ "		and SITE_ID in (:ordersources) "
						+ 		firstChannelSQL + secondChannelSQL
						+ ") d "
						+ " group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
		 } else if (name == ChartDefine.NEW_CLIENT_RATE) {
			 sql = "select to_char(d.DATE_DAY, 'YYYY-MM-DD') as dataDate, "
						+ "d.T_HOUR as dataHour, "
						+ "round(sum(d.NEW_CLIENT) / sum(d.CLIENT_NUMBER), 4) as NEW_CLIENT_RATE "
						+ "from ("
						+ "		select CLIENT_NUMBER, "
						+ "		NEW_CLIENT, "												
						+ "		T_HOUR, "
						+ "		SITE_ID, "
						+ "		DATE_DAY, "
						+ "		UTM_MEDIUM,"
						+ "		UTM_SOURCE "
						+ "		from DV_LIVE_PLATFORM_SUMMARIZE "
						+ "		where DATE_DAY in (date'"+today+"', date'"+contrastday+"') "
						+ "		and SITE_ID in (:ordersources) "
						+ 		firstChannelSQL + secondChannelSQL
						+ ") d "
						+ " group by d.DATE_DAY, d.T_HOUR order by d.DATE_DAY, d.T_HOUR";
		 } else {
			
		 }
		 
//		 System.err.println("WebImpl->getLineChartDataxxxxxx"+sql);
		// Query
		if (!sql.isEmpty()) {
			NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			parameters.addValue("ordersources", ordersources);
		    rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		}
	        
	    return rows;
	}
	 
	 /**
	  * 关键词表格
	  * @param today
	  * @param contrastday
	  * @param ordersources 平台ID链表
	  * @param firstChannel	一级渠道
	  * @param secondChannel	二级渠道
	  * @return
	  */
	 public List<KeywordInforEntity> getKeywordTableData(String today, String contrastday, String ordersources, String firstChannel, String secondChannel) {
		 String firstChannelSQL = "";
		 String secondChannelSQL = "";
		 
		 if (!StringUtils.isBlank(firstChannel))
			 firstChannelSQL = " and department = '" + firstChannel + "' ";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 String sql = "select t.KEYWORD as KEYWORD, "
			   		+ "sum(t.VISITORS) as VISITORS, "
			   		+ "nvl(round(sum(t.JUMP_NUMBER) / sum(t.SOURCE_NUMBER), 4),0) as JUMP_CONV_RATE, "
			   		+ "nvl(round(sum(t.DEPTH) / sum(t.VISITORS), 0),0) as AVG_DEPTH "
			   		+ "from (select KEYWORD, "
			   			+ "VISITORS, "
			   			+ "JUMP_NUMBER, "
			   			+ "SOURCE_NUMBER, "
			   			+ "VISITORS * AVG_DEPTH as DEPTH, "
			   			+ "DATE_DAY, "
			   			+ "SITEID, "
			   			+ "UTM_MEDIUM, "
			   			+ "UTM_SOURCE "
			   			+ "from DV_OUTSIDE_KEYWORD "
			   			+ "where DATE_DAY in(date'" + today + "', date'" + contrastday + "') "
				   		+ "and SITEID in(" + ordersources + ") "
				   		+ firstChannelSQL + secondChannelSQL
				   		+ " and DATE_DAY = trunc(sysdate) "
			   			+ ") t "			   		
			   		+ " group by t.KEYWORD order by VISITORS DESC";
		 
//		 System.out.println("关键字表格： " + sql);
		 
		 List<KeywordInforEntity> result = mJdbcTemplate.query(sql, new RowMapper<KeywordInforEntity>() {

			@Override
			public KeywordInforEntity mapRow(ResultSet resultSet, int row) throws SQLException {
				KeywordInforEntity entity = new KeywordInforEntity();
				entity.setKEYWORD(resultSet.getString("KEYWORD"));
				entity.setVISITORS(resultSet.getInt("VISITORS"));
				entity.setJUMP_CONV_RATE(resultSet.getFloat("JUMP_CONV_RATE"));
				entity.setAVG_DEPTH(resultSet.getFloat("AVG_DEPTH"));
				return entity;
			}
			 
		 });
		        
		 return result;
	 }
	 
	 /**
	  * 关键字来源区域表格
	  * @param today
	  * @param contrastday
	  * @param ordersources 平台ID链表
	  * @param firstChannel 一级渠道
	  * @param secondChannel 二级渠道
	  * @param keyword		关键字
	  * @return
	  */
	 public List<RegionInforEntity> getRegionTableData(String today, String contrastday, String ordersources, String firstChannel, String secondChannel, String keyword) {
		 String firstChannelSQL = "";
		 String secondChannelSQL = "";
		 String keywordSQL = "";
		 
		 if (!StringUtils.isBlank(firstChannel))
			 firstChannelSQL = " and department = '" + firstChannel + "' ";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 if (!StringUtils.isBlank(keyword))
			 keywordSQL = " and KEYWORD = '" + keyword + "' ";
		 
		 String sql = "select t.PROVINCE as PROVINCE, "
			   		+ "sum(t.VISITORS) as VISITORS, "
			   		+ "nvl(round(sum(t.JUMP_NUMBER) / sum(t.SOURCE_NUMBER), 4),0) as JUMP_CONV_RATE, "
			   		+ "nvl(round(sum(t.DEPTH) / sum(t.VISITORS), 0),0) as AVG_DEPTH, "
			   		+ "from (select PROVINCE, "
			   			+ "VISITORS, "
			   			+ "JUMP_NUMBER, "
			   			+ "SOURCE_NUMBER, "
			   			+ "VISITORS * AVG_DEPTH as DEPTH, "
			   			+ "DATE_DAY, "
			   			+ "SITEID, "
			   			+ "UTM_MEDIUM, "
			   			+ "UTM_SOURCE "
			   			+ "from DV_OUTSIDE_KEYWORD "
			   			+ "where DATE_DAY in(date'" + today + "', date'" + contrastday + "') "
				   		+ "and SITEID in(" + ordersources + ") "
				   		+ firstChannelSQL + secondChannelSQL + keywordSQL
				   		+ " and DATE_DAY = trunc(sysdate) "
			   			+ ") t "
			   		+ " group by t.PROVINCE order by VISITORS DESC";
		 
//		 System.out.println("地域表格SQL： " + sql);
		 
		 List<RegionInforEntity> result = mJdbcTemplate.query(sql, new RowMapper<RegionInforEntity>() {

			@Override
			public RegionInforEntity mapRow(ResultSet resultSet, int row) throws SQLException {
				RegionInforEntity entity = new RegionInforEntity();
				entity.setPROVINCE(resultSet.getString("PROVINCE"));
				entity.setVISITORS(resultSet.getInt("VISITORS"));
				entity.setJUMP_CONV_RATE(resultSet.getFloat("JUMP_CONV_RATE"));
				entity.setAVG_DEPTH(resultSet.getFloat("AVG_DEPTH"));
				return entity;
			}
			 
		 });
		        
		 return result;
	 }
	 
	 public Object getKeywordMaxVisitor(String today, String contrastday, String ordersources, String firstChannel, String secondChannel) {
		 String secondChannelSQL = "";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 String sql = "SELECT *"
		 +"FROM (SELECT SUM(VISITORS) AS MaxVisitor"
		 +"         FROM DV_OUTSIDE_KEYWORD"
		 +"        WHERE DATE_DAY = TRUNC(SYSDATE)"
		 +"          AND SITEID IN (" + ordersources + ")"
		 +"          AND DEPARTMENT IN ('" + firstChannel + "')"
		 +secondChannelSQL
		 +"        GROUP BY SOURCEKEYWORD"
		 +"        ORDER BY MaxVisitor DESC)"
		 +"WHERE ROWNUM <= 1";
		 
		 List<Map<String,Object>> rows = mJdbcTemplate.queryForList(sql);
		 
		 return rows.size() > 0 ? rows.get(0).get("MaxVisitor") : 0;
	 }
	 
	 public Object getRegionMaxVisitor(String today, 
			 						   String contrastday, 
			 						   String ordersources, 
			 						   String firstChannel, 
			 						   String secondChannel, 
			 						   String keyword ) {
		 String secondChannelSQL = "";
		 String keywordSQL = "";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 if (!StringUtils.isBlank(keyword))
			 keywordSQL = " and SOURCEKEYWORD = '" + keyword + "' ";
		 
		 String sql = "SELECT *"
		 +"FROM (SELECT SUM(VISITORS) AS MaxVisitor"
		 +"         FROM DV_OUTSIDE_KEYWORD"
		 +"        WHERE DATE_DAY = TRUNC(SYSDATE)"
		 +"          AND SITEID IN (" + ordersources + ")"
		 +"          AND DEPARTMENT IN ('" + firstChannel + "')"
		 +secondChannelSQL
		 +keywordSQL
		 +"        GROUP BY PROVINCE "
		 +"        ORDER BY MaxVisitor DESC)"
		 +"WHERE ROWNUM <= 1";
		 
		 List<Map<String,Object>> rows = mJdbcTemplate.queryForList(sql);
		 
		 return rows.size() > 0 ? rows.get(0).get("MaxVisitor") : 0;
	 }
	 
	 /***
	  * 登录页
	  * @param today
	  * @param contrastday
	  * @param ordersources
	  * @param channel		一级渠道
	  * @param landpage		登录页
	  * @return
	  */
	 public List<Map<String, Object>> getLandpageOverview(String today, String contrastday, List<String> ordersources, String channel, String landpage) {
		 String landpageSQL = "";
		 
		 if (!StringUtils.isBlank(landpage))
			 landpageSQL = " and LANDPAGE = '" + landpage + "' ";
		 
		 String sql = "select sum(t.CLIENT_NUMBER) as CLIENT_NUMBER, "
			   		+ "sum(t.PV) as PV, "
			   		+ "sum(t.NEW_CLIENT) as NEW_CLIENT, "			 
			   		+ "nvl(round(SUM(t.VISITDEPTH) / SUM(t.CLIENT_NUMBER), 2), 0) as AVG_VISITDEPTH, "
			   		+ "nvl(round((SUM(ABS(t.SUM_VISITLENGTH)) / sum(t.MULTI_PV_CLIENT)) / 1000, 2), 0) as AVG_VISITLENGTH "
			   		+ "from "
			   		+ "( "
			   			+ "select CLIENT_NUMBER, "
			   			+ "PV, "
			   			+ "NEW_CLIENT,"
			   			+ "SUM_VISITLENGTH,"
			   			+ "MULTI_PV_CLIENT,"
			   			+ "DATE_DAY,"
			   			+ "SITE_ID,"
			   			+ "CLIENT_NUMBER * AVG_VISITDEPTH as VISITDEPTH "
			   			+ "from DV_LIVE_PLATFORM_SUMMARIZE "
			   			+ "where DATE_DAY in(date'" + today + "', date'" + contrastday + "') " 
				   		+ "and SITE_ID in(:ordersources) "
				   		+ "and department = '" + channel + "' "
				   		+ landpageSQL
			   		+ ") t "
			   		+ " group by trunc(t.DATE_DAY) order by trunc(t.DATE_DAY)";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 parameters.addValue("ordersources", ordersources);
		 
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		        
		 return rows;
	 }
	 
	 /**
	  * 登录页跳出率和占比
	  * @param today
	  * @param contrastday
	  * @param ordersources
	  * @param firstChannel		一级渠道
	  * @param secondChannel	二级渠道
	  * @return
	  */
	 public List<Map<String, Object>> getLandpageJumpRate(String today, String contrastday, List<String> ordersources, String firstChannel, String secondChannel) {
		 String secondChannelSQL = "";
		 
		 if (!StringUtils.isBlank(secondChannel))
			 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
		 
		 String sql = "select t.LANDPAGE, "
		 			+ "nvl(round(sum(JUMP_NUMBER) / sum(t.SOURCE_NUMBER), 4), 0) as JUMP_CONV_RATE, "
		 			+ "nvl(round(sum(t.CLIENT_NUMBER) / max(t.totalClientNumber), 4), 0) as CLIENT_RATE "
			   		+ "from (select CLIENT_NUMBER, "
			   			+ "LANDPAGE, "
			   			+ "JUMP_RATE, "
			   			+ "JUMP_NUMBER, "
						+ "SOURCE_NUMBER, "
			   			+ "sum(CLIENT_NUMBER) OVER(PARTITION BY site_id) as totalClientNumber, "
			   			//+ "CLIENT_NUMBER * JUMP_RATE as JUMP_CLIENT, "
			   			+ "SITE_ID, "
			   			+ "UTM_MEDIUM, "
			   			+ "UTM_SOURCE, "
			   			+ "DATE_DAY "
			   			+ "from DV_LIVE_PLATFORM_SUMMARIZE "
			   			+ "where DATE_DAY in(date'" + today + "') " 
				   		+ "and SITE_ID in(:ordersources) "
				   		+ "and department = '" + firstChannel + "' "
				   		+ secondChannelSQL
			   			+ ") t "
			   		+ " group by t.LANDPAGE";
		 
//		 System.out.println("+++++++ sql " + sql);
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 parameters.addValue("ordersources", ordersources);
		 
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		        
		 return rows;
	 }
	 
	 /**
	  * 登录页模块-客户数占比饼状图
	  * @param today
	  * @param contrastday
	  * @param ordersources
	  * @param firstChannel		一级渠道
	  * @param secondChannel	二级渠道
	  * @return
	  */
	 public Map<String,Object> getClientProportion(String today, String contrastday, List<String> ordersources, String firstChannel, String secondChannel) {
	        Map resultMap = Maps.newHashMap();
	        List<Map<String,String>> list = Lists.newArrayList();
	         	               
	        String secondChannelSQL = "";
			 
			 if (!StringUtils.isBlank(secondChannel))
				 secondChannelSQL = " and UTM_SOURCE = '" + secondChannel + "' ";
			 
			 String sql = "select t.LANDPAGE, "
			 			+ "nvl(round(sum(t.CLIENT_NUMBER) / max(t.totalClientNumber), 4), 0) as CLIENT_RATE "
				   		+ "from ("
				   		+ "		select CLIENT_NUMBER, LANDPAGE, JUMP_RATE,"
				   		+ "		sum(CLIENT_NUMBER) OVER(PARTITION BY site_id) as totalClientNumber, SITE_ID, UTM_MEDIUM, "
				   		+ "		UTM_SOURCE, DATE_DAY "
				   		+ "		from DV_LIVE_PLATFORM_SUMMARIZE "
				   		+ "	 	where DATE_DAY in(date'" + today + "') " 
				   		+ "		and SITE_ID in (:ordersources) "
				   		+ "		and department = '" + firstChannel + "' "+secondChannelSQL
				   		+ "     and landpage is not null"
				   		+ ") t "
				   		+ " group by t.LANDPAGE";
	        
	        // Query
	        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			parameters.addValue("ordersources", ordersources);
	        List<Map<String,Object>> queryList = namedParameterJdbcTemplate.queryForList(sql, parameters);
	        
	        // 饼图数据
	        String[] legendArr = new String[queryList.size()];
	        for(int i=0, len=queryList.size(); i<len; i++) {
	        	String platformName = String.valueOf(queryList.get(i).get("LANDPAGE"));
	            
	            legendArr[i] = String.valueOf(Translate(platformName));
	            
	            Map<String,String> map = Maps.newHashMap();
	            map.put("value",String.valueOf(queryList.get(i).get("CLIENT_RATE")));
	            map.put("name",String.valueOf(Translate(platformName)));
	            list.add(map);
	        }
	        resultMap.put("data", list);
	        resultMap.put("legend", legendArr);
	        
	        return  resultMap;
	 }
	 
	 /**
	  * 翻译英语为中文
	  * @param english
	  * @return
	  */
	 private String Translate(String english) {
		 String chinese = "";
		 
		 if ("category".equals(english)) {
			 chinese = "分类页";
		 } else if ("zhuanti".equals(english)) {
			 chinese = "专题页";
		 } else if ("index".equals(english)) {
			 chinese = "首页";
		 } else if ("product".equals(english)) {
			 chinese = "商品详情页";
		 } else if ("other".equals(english)) {
			 chinese = "其他";
		 } else if ("information".equals(english)) {
			 chinese = "资讯页";
		 } else {
			 System.out.println("english = " + english);
		 }
		 
		 return chinese;
	 }
}















